if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapPSDemogFeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapPSDemogFeed]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			MapPSDemogFeed
* Purpose:		Using table Stg_IE_DemogFeed, map & seed Insight IDs, and validate
*				feed data.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE [dbo].[MapPSDemogFeed]
AS
BEGIN

	SET NOCOUNT ON

/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S A V E  A G R E E M E N T  S T A T U S E S
	We use the awarded and closed statuses later in the code.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
DECLARE @AWARDED int
DECLARE @CLOSED int

select @AWARDED = ID from agragreementStatusType where [name] = 'Awarded'
select @CLOSED = ID from agragreementStatusType where [name] = 'Closed'

/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	R E P A I R  B A D  D A T A  F R O M  P S  F E E D
	Due to bad data we receive in the PeopleSoft Feed, the 3 updates
	below are necessary in order to correct the sponsor and award
	type data.  These corrections avoid unnecessary errors.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	-- Remove colons and versions from folder_number
	update Stg_IE_DemogFeed set folder_number=left(folder_number,11)
	
	-- Convert blank sponsors to be the 'sundry' sponsor
	update Stg_IE_DemogFeed set sponsor_name = 'SUNDRY' 
	where isNull(sponsor_name,'')='' or IsNumeric(isNull(sponsor_name,''))=1

	-- Convert the sponsor code to sundry for any fund with a sponsor name of
	-- sundry and the sponsor code is null or blank.
	update Stg_IE_DemogFeed set sponsor_code = '0' ,  sponsor_code_id = 0
	where isNull(sponsor_code,'')='' 

	-- Set the award type to 'misc/other' award type if it is null or blank.
	update Stg_IE_DemogFeed set award_type_code = 'M' 
	where isNull(award_type_code,'')='' 

	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- E X I S T I N G  F U N D  A N D  F O L D E R  N U M B E R
	-- First map fund number to existing fund and agreement data
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

	UPDATE s
	SET AgrAgreementId = a.Id
		,folder_number = a.foldernumber
		,SvcEntityId = a.SvcEntityId
		,AgrBudgetContainerId = a.AgrBudgetContainerId
		,AgrBudgetContainerViewId = bcv.Id
		,AgrBudgetId = b.Id
		,AgrBudgetCount = (Select Count(*) from AgrBudget b3 where b3.AgrBudgetContainerViewId = bcv.Id and b3.AgrBudgetTypeId = 1) --annual budget
		,AgrBudgetICRateId = bicr.id
		,FwkResourceId = a.FwkResourceId
		,AnnAnnotationContainerId = a.AnnAnnotationContainerId
		,SvcEntityFacilityMapId = afm.Id
		,AgrFundId = f.AgrFundID
		,AgrFundAgreementMapId = f.Id
		,BusinessUnitId = f.BusinessUnitId
		,Orig_AgrNumberSchemeTypeId = a.AgrNumberSchemeTypeId
		,Orig_AgrTransactionTypeId = a.AgrTransactionTypeId
		,Orig_AgrAgreementTypeId = a.AgrAgreementTypeId
		,Orig_AgrAgreementStatusTypeId = a.AgrAgreementStatusTypeId
		,Orig_PI_AdmPersonID = a.PIPersonID
		,Orig_RM_AdmPersonID = RM.AdmPersonID
		,Orig_CSR_AdmPersonID = CSRL.AdmPersonID
		,Orig_AdmSponsorId = spm.AdmSponsorId
		,Orig_fwkDomainOrganizationId = fdo.fwkDomainOrganizationId
		,Orig_finICBaseTypeId = bc.IndirectCostBaseId
		,Orig_AgrActivityTypeId = act.Id
		,Orig_FinAccountCycleTypeId = f.FinAccountCycleTypeId
		,Orig_AgrFundStatusTypeId = f.AgrFundStatusTypeId
		,Orig_AgrFundAwardTypeId = f.AgrFundAwardTypeId
		,Orig_AgrFundOverheadClassificationId = f.AgrFundOverheadClassificationId
		,Orig_AgrFundTypeId = f.AgrFundTypeId
		,Orig_AgrFundSubTypeId = f.AgrFundSubTypeId
		,Orig_AgrCostCenterId = cfm.AgrCostCenterId
		,Orig_AgrCostCenterStatusTypeId = cfm.AgrCostCenterStatusTypeId
		,Orig_AdmFacilityId = afm.AdmFacilityId
		,IsNewAgreement = 0
		,IsNewFund = 0
	from Stg_IE_DemogFeed s
	inner join AgrFund_AgrAgreement_Map f on f.Fundnumber = s.project_grant
	inner join AgrAgreement a on a.id = f.agragreementId and a.documentnumber = (Select max(a1.documentNumber) from AgrAgreement a1
															where a1.foldernumber = a.folderNumber 
																and a1.isAmendment = 0 and a1.IsCurrent=1)
	left outer join SvcEntity_FwkDomainOrganization_Map fdo on fdo.svcentityid = a.svcentityid and fdo.FwkOrganizationRelationshipTypeId = 1 -- Primary 
	left outer join SvcEntity_AdmAdministrator_Map RM on RM.svcentityid = a.svcentityid and RM.AdministratorTypeId = 1 -- RM admin
	left outer join SvcEntity_AdmAdministrator_Map CSRL on CSRL.svcentityid = a.svcentityid and CSRL.AdministratorTypeId = 2 -- CSRL admin
	left outer join SvcEntity_AdmFacility_Map afm on afm.svcentityid = a.svcentityid and afm.AdmFacilityRelationshipTypeId = 1 -- primary facility
	left outer join SvcEntity_AdmSponsor_Map spm on spm.svcentityid = a.svcentityid and spm.AdmSponsorRelationTypeId = 1 -- primary sponsor.
	left outer join AgrBudgetContainer bc on bc.Id = a.AgrBudgetContainerId
	left outer join AgrBudgetContainerView bcv on bcv.AgrBudgetContainerId = a.AgrBudgetContainerId and bcv.AgrBudgetViewTypeId = 3 --awarded
	left outer join AgrBudget b on b.agrFundAgreeMapId = f.id
	left outer join AgrBudgetICRate bicr on bicr.AgrBudgetId = b.Id 
	left outer join AgrActivityType act on act.PSCode = s.activity_type_code
	left outer join AgrCostCenter_AgrFund_Map cfm on cfm.AgrFundAgreementMapId = f.Id
	left outer join AgrCostCenter cc on cc.Id = cfm.AgrCostCenterId 
	left outer join AgrCostCenterStatusType ccs on ccs.Id = cfm.AgrCostCenterStatusTypeId

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	A P P L Y  F U N D  A C T I V A T I O N  D A T E S  F R O M  I N F O E D
	PeopleSoft demog.txt does not have the fund activation dates so we are
	loading them from InfoEd and defaulting to effective date if the 
	activation date is not available.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	update s 
	set activationDate = coalesce(s1.dtActivated,s.effective_date)	
	from Stg_IE_DemogFeed s
	left outer join Stg_ie_DemogFeed_InfoEdFunds s1 on s.project_grant = s1.sPSFundNumber

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	A P P L Y  F O L D E R  N U M B E R  F R O M  I N F O E D
	Folder numbers in PeopleSoft tend to be inaccurate, so where
	available, we are mapping the folder numbers to the appropriate
	fund numbers from InfoEd.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	update s 
	set folder_number = left(s1.sFolderNumber,11)
		,activationDate = s1.dtActivated	
		,IsInfoEdFolderNumber = 1
	from Stg_IE_DemogFeed s
	inner join Stg_ie_DemogFeed_InfoEdFunds s1 on s.project_grant = s1.sPSFundNumber
	where (isnull(s.folder_number,'')='' or isnull(s.folder_number,'')<> left(s1.sFolderNumber,11)) and agragreementId is null

	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- N E W  F U N D  O N  E X I S T I N G  F O L D E R  N U M B E R
	-- If the folder number data was not mapped through an existing
	-- fund number, try to map to an existing folder number.
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

	UPDATE s
	SET AgrAgreementId = a.Id
		,folder_number = a.foldernumber
		,SvcEntityId = a.SvcEntityId
		,AgrBudgetContainerId = a.AgrBudgetContainerId
		,AgrBudgetContainerViewId = bcv.Id
		,AgrBudgetId = b.Id
		,AgrBudgetCount = (Select Count(*) from AgrBudget b3 where b3.AgrBudgetContainerViewId = bcv.Id and b3.AgrBudgetTypeId = 1) --annual budget
		,AgrBudgetICRateId = bicr.id
		,FwkResourceId = a.FwkResourceId
		,AnnAnnotationContainerId = a.AnnAnnotationContainerId
		,SvcEntityFacilityMapId = afm.Id
		,AgrFundId = f.ID
		,BusinessUnitId = f.BusinessUnitId
		,Orig_FinAccountCycleTypeId = f.FinAccountCycleTypeId
		,Orig_AgrFundStatusTypeId = f.AgrFundStatusTypeId
		,Orig_AgrFundAwardTypeId = f.AgrFundAwardTypeId
		,Orig_AgrFundOverheadClassificationId = f.AgrFundOverheadClassificationId
		,Orig_AgrFundTypeId = f.AgrFundTypeId
		,Orig_AgrFundSubTypeId = f.AgrFundSubTypeId
		,Orig_AgrNumberSchemeTypeId = a.AgrNumberSchemeTypeId
		,Orig_AgrTransactionTypeId = a.AgrTransactionTypeId
		,Orig_AgrAgreementTypeId = a.AgrAgreementTypeId
		,Orig_AgrAgreementStatusTypeId = a.AgrAgreementStatusTypeId
		,Orig_PI_AdmPersonID = a.PIPersonID
		,Orig_RM_AdmPersonID = RM.AdmPersonID
		,Orig_CSR_AdmPersonID = CSRL.AdmPersonID
		,Orig_AdmSponsorId = spm.AdmSponsorId
		,Orig_fwkDomainOrganizationId = fdo.fwkDomainOrganizationId
		,Orig_finICBaseTypeId = bc.IndirectCostBaseId
		,Orig_AgrActivityTypeId = act.Id
		,Orig_AdmFacilityId = afm.AdmFacilityId
		,IsNewFund = case when f.id is null then 1 else 0 end
		,IsNewAgreement = 0
	from Stg_IE_DemogFeed s
	inner join AgrAgreement a  on a.FolderNumber = s.folder_number  
									and a.documentNumber = (Select max(a1.documentNumber) from AgrAgreement a1
															where a1.foldernumber = a.foldernumber
																and a1.isAmendment = 0 and a1.IsCurrent=1)
	left outer join SvcEntity_FwkDomainOrganization_Map fdo on fdo.svcentityid = a.svcentityid and fdo.FwkOrganizationRelationshipTypeId = 1 -- Primary 
	left outer join SvcEntity_AdmAdministrator_Map RM on RM.svcentityid = a.svcentityid and RM.AdministratorTypeId = 1 -- RM admin
	left outer join SvcEntity_AdmAdministrator_Map CSRL on CSRL.svcentityid = a.svcentityid and CSRL.AdministratorTypeId = 2 -- CSRL admin
	left outer join SvcEntity_AdmFacility_Map afm on afm.svcentityid = a.svcentityid and afm.AdmFacilityRelationshipTypeId = 1 -- primary facility
	left outer join SvcEntity_AdmSponsor_Map spm on spm.svcentityid = a.svcentityid and spm.AdmSponsorRelationTypeId = 1 -- primary sponsor.
	left outer join AgrBudgetContainer bc on bc.Id = a.AgrBudgetContainerId
	left outer join AgrBudgetContainerView bcv on bcv.AgrBudgetContainerId = a.AgrBudgetContainerId and bcv.AgrBudgetViewTypeId = 3 --awarded
	left outer join AgrBudget b on b.AgrBudgetContainerViewId = bcv.Id and b.AgrBudgetTypeId = 1 --annual budget
									and b.Id = (select max(b1.Id) from AgrBudget b1 
												where b1.AgrBudgetContainerViewId = b.AgrBudgetContainerViewId 
													and b1.AgrBudgetTypeId = b.AgrBudgetTypeId
													and b1.startDate = coalesce(s.dtBudgetStart,s.dtprojectstart,'1/1/1900'))
	left outer join AgrBudgetICRate bicr on bicr.AgrBudgetId = b.Id 
	left outer join AgrActivityType act on act.PSCode = s.activity_type_code
	left outer join AgrFund f on f.FundNumber = s.project_grant
	where IsNull(s.AgrFundId,0)=0 and IsNull(s.folder_number,'')<>''

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	M A P  N E W  T Y P E  D A T A
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE	Stg_IE_DemogFeed
	SET		/*
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*** Default to NEW Can't assign because we don't know if 
			it is NEW, NON-COMPETE, TRANSFER, COMPETING RENEWAL, or SUPPLIMENT.
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*/
			agrTransactionTypeId = coalesce(Orig_AgrTransactionTypeId,1)
													 -- Default NEW transaction
			/*
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*** Active = AWARDED. Inactive = CLOSED
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*/
			,AgrAgreementStatusTypeId = case when IsNull(effective_status,'')='A' then 
													@AWARDED
											 else	@CLOSED
										end
			/*
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			M A P  A G R E E M E N T  T Y P E S
			Map the agreement types to IDs in Insight AgrAgreementType table
			from the award type code of the fund.
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*/
			,AgrAgreementTypeId = coalesce(
					(Select fat.AgrAgreementTypeId from AgrFundAwardType fat
					 where fat.[name] = isNull(award_type_code,''))
					,Orig_AgrAgreementTypeId
					,1/* Grant is the default if all else fails */)
			/*
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			M A P  F U N D  A W A R D  T Y P E S
			Map the fund award types to IDs in Insight AgrFundAwardType table.
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*/
			,AgrFundAwardTypeId = 
									(select AgrFundAwardType.Id from AgrFundAwardType 
									where AgrFundAwardType.[name] = isNull(award_type_code,''))

			,AgrFundTypeId = 1 --Research

			/*
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			M A P  I C  B A S I S  C O D E S
			Map the IC basis codes to IDs in Insight finICBaseType table.
			>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			*/
			,finICBaseTypeId = coalesce(
								(select p.finICBaseTypeId from FinPSICBaseType p 
								 where p.[id] = isNull(ic_base_int,0))
								,1/* Default to MTDC if nothing else */)

			-- if can't map, default to miscellaneous activity type.
			,AgrActivityTypeId = coalesce(
								 (Select AgrActivityType.Id from AgrActivityType 
								  where AgrActivityType.PSCode = IsNull(activity_type_code,''))
								 ,21) 

			,AgrFundOverheadClassificationId = 
								(Select AgrFundOverheadClassification.id from AgrFundOverheadClassification 
								 where AgrFundOverheadClassification.PSCode = IsNull(Fund_code,''))

			,IsResearchProject = 
								(Select AgrFundOverheadClassification.IsResearchProject from AgrFundOverheadClassification 
								 where AgrFundOverheadClassification.PSCode = IsNull(Fund_code,''))

			,AgrFundStatusTypeId = 
								(Select AgrFundStatusType.Id from AgrFundStatusType 
								 where  AgrFundStatusType.[name] = IsNull(effective_status,''))

			,FinAccountCycleTypeId = coalesce(
								(Select FinAccountCycleType.Id from FinAccountCycleType 
								 where	upper(left(FinAccountCycleType.[name],1)) = 
										Upper(ltrim(rtrim(IsNull(acct_cycle_code,'')))))
								, Orig_FinAccountCycleTypeId)

			,fwkDomainOrganizationId = 
								(select Id from fwkDomainOrganization 
								 where fwkDomainOrganization.Institution = IsNull(resp_inst,'')
									and fwkDomainOrganization.ChiefCode = IsNull(dept_code,''))

			-- Map the PI ID from first the name conversion table, then the original if it exists
			-- and matches the last name, otherwise name match exactly on last name and 
			-- first name from PeopleSoft.
			,PI_AdmPersonId =	coalesce(
								 (select min(ap.id) from fwkdomainuser fdu 
								  inner join admperson ap on ap.fwkdomainuserid = fdu.id 
								  where fdu.username = PILoginId and isNull(PILoginId,'')<>'')
								,(Select l.AdmPersonId from PINameToPersonId l 
									Where l.lastname = IsNull(mgr_last_name,'') and l.firstname = IsNull(mgr_first_name,''))
								,(select ap.Id from admperson ap
										  where  (ap.Id = Orig_PI_AdmPersonId and (ap.lastname = IsNull(mgr_last_name ,'') or IsNull(mgr_last_name ,'')='')
											and Orig_PI_AdmPersonId is not null))								
								,(Select min(s.Id) from
									(select Id,len(ltrim(rtrim(firstname))) as FirstNameLength from admPerson 
									where lastname = IsNull(mgr_last_name,'') 
										and firstname = left(IsNull(mgr_first_name,''),len(ltrim(rtrim(firstname))))) s) )

			,AdmSponsorId =	coalesce((select min(s.id) from admsponsor s where s.pscustcode = CustomerID  and IsNull(CustomerID,'')<>'')
									,Orig_AdmSponsorId
									,(Select min(spsr.Id) from AdmSponsor spsr where spsr.[name] = IsNull(sponsor_name,'')
									  or spsr.[Shortname] = IsNull(sponsor_name,'') or spsr.[Description] = IsNull(sponsor_name,'')))

			,AgrCostCenterId = (Select min(ac.Id)  FROM AgrCostCenter ac 
								Where ac.Number = IsNull(Cost_Center,'')  
									and ((IsNull(resp_inst,'')='MGH' and ac.agrcostcentersystemtypeid = 1)
										or(IsNull(resp_inst,'') = 'BWH' and ac.agrcostcentersystemtypeid = 2)))

			,AgrCostCenterStatusTypeId = (Select AgrCostCenterStatusType.Id from AgrCostCenterStatusType 
								 where  AgrCostCenterStatusType.[name] = IsNull(effective_status,''))

			,AdmFacilityId = (Select AdmFacility.id from AdmFacility where AdmFacility.PSCode = IsNull(location_code,''))

			,BusinessUnitId = (Select AdmBusinessUnit.Id from AdmBusinessUnit where PSCode = right('0000'+cast(IsNull(Business_unit,0) as varchar),4))

			,AgrFundId = (Select AgrFund.Id from AgrFund where AgrFund.FundNumber = IsNull(project_grant,''))
			,IsAgreementMaintained = case when (exists(select 1 from Stg_ie_DemogFeed_InfoEdFunds s1 
														where project_grant = s1.sPSFundNumber))
										  then 0 
										  else 1 
									  end
			,IsNewFund = case when exists(Select 1 from AgrFund where AgrFund.FundNumber = IsNull(project_grant,'')) Then 0 else 1 end
			,IsCurrentFund = 0 -- Not the current fund on the agreement.
			,IsChanged = 0 -- Not changed
			,IsNewCostCenter = 0 --Not New Cost Center
			,IsNewSponsor = 0 -- Not New Sponsor
			,IsValid = 1 -- Valid data

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  N E W  S P O N S O R 
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE	Stg_IE_DemogFeed
	SET		IsNewSponsor = 1
	WHERE	AdmSponsorId is null and rtrim(ltrim(isnull(sponsor_name,'')))<> ''
			and IsAgreementMaintained = 1
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  N E W  C O S T  C E N T E R 
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE	Stg_IE_DemogFeed
	SET		IsNewCostCenter = 1
	WHERE	AgrCostCenterId is null and rtrim(ltrim(isnull(cost_center,'')))<> ''
			and IsNull(resp_inst,'') = 'BWH'

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  E R R O R S
	Look for stuff that does not map
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE	Stg_IE_DemogFeed
	SET		IsValid = 0
	Where
		   (isNull(PI_AdmPersonId,0) = 0 and IsAgreementMaintained = 1)
		or (isNull(AdmSponsorId,0) = 0 and IsNewSponsor = 0 and IsAgreementMaintained = 1)
		or isNull(agrTransactionTypeId,0) = 0
		or isNull(AgrAgreementStatusTypeId,0) = 0
		or isNull(AgrAgreementTypeId,0) = 0
		or isNull(finICBaseTypeId,0) = 0
		or isNull(AgrActivityTypeId,0) = 0
		or isNull(FinAccountCycleTypeId,0) = 0
		or isNull(fwkDomainOrganizationId,0) = 0
		or (isNull(AgrCostCenterId,0) = 0 and IsNewCostCenter = 0 and IsNull(resp_inst,'') = 'BWH')
		or isNull(AgrFundAwardTypeId,0) = 0
		or isNull(AgrFundOverheadClassificationId,0) = 0
		or isNull(AgrFundStatusTypeId,0) = 0
		or isNull(AgrCostCenterStatusTypeId,0) = 0
		or isNull(BusinessUnitId,0) = 0

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  I D s
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrIDs')
		DROP TABLE tmpSeededAgrIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededSvcIDs')
		DROP TABLE tmpSeededSvcIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFundSvcIDs')
		DROP TABLE tmpSeededFundSvcIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFwkIDs')
		DROP TABLE tmpSeededFwkIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededBgtIDs')
		DROP TABLE tmpSeededBgtIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAnnIDs')
		DROP TABLE tmpSeededAnnIDs

	DECLARE	@_agreementSeed		int
	DECLARE	@_entitySeed		int
	DECLARE	@_resourceSeed		int
	DECLARE	@_budgetContainerSeed		int
	DECLARE	@_annotationSeed	int

	IF (SELECT Count(Id) FROM AgrAgreement) = 0
		SET	@_agreementSeed		= 1
	ELSE
		SET	@_agreementSeed		= IDENT_CURRENT('AgrAgreement') + 1

	IF (SELECT Count(Id) FROM SvcEntity) = 0
		SET	@_entitySeed		= 1
	ELSE
		SET	@_entitySeed		= IDENT_CURRENT('SvcEntity') + 1

	IF (SELECT Count(Id) FROM FwkResource) = 0
		SET	@_resourceSeed		= 1
	ELSE
		SET	@_resourceSeed		= IDENT_CURRENT('FwkResource') + 1

	IF (SELECT Count(Id) FROM AgrBudgetContainer) = 0
		SET	@_budgetContainerSeed		= 1
	ELSE
		SET	@_budgetContainerSeed		= IDENT_CURRENT('AgrBudgetContainer') + 1

	IF (SELECT Count(Id) FROM AnnAnnotationContainer) = 0
		SET	@_annotationSeed	= 1
	ELSE
		SET	@_annotationSeed	= IDENT_CURRENT('AnnAnnotationContainer') + 1

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	F U N D  S E E D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFundIDs')
		DROP TABLE tmpSeededFundIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFundAgreeMapIDs')
		DROP TABLE tmpSeededFundAgreeMapIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededBudgetContainerViewIDs')
		DROP TABLE tmpSeededBudgetContainerViewIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededBudgetIDs')
		DROP TABLE tmpSeededBudgetIDs

	DECLARE	@_fundSeed					int
	DECLARE	@_fundAgreeMapSeed			int
	DECLARE	@_budgetContainerViewSeed	int
	DECLARE	@_budgetSeed				int

	IF (SELECT Count(Id) FROM AgrFund) = 0
		SET	@_fundSeed	= 1
	ELSE
		SET	@_fundSeed	= IDENT_CURRENT('AgrFund') + 1

	IF (SELECT Count(Id) FROM AgrFund_AgrAgreement_Map) = 0
		SET	@_fundAgreeMapSeed	= 1
	ELSE
		SET	@_fundAgreeMapSeed	= IDENT_CURRENT('AgrFund_AgrAgreement_Map') + 1

	IF (SELECT Count(Id) FROM AgrBudgetContainerView) = 0
		SET	@_budgetContainerViewSeed	= 1
	ELSE
		SET	@_budgetContainerViewSeed	= IDENT_CURRENT('AgrBudgetContainerView') + 1

	IF (SELECT Count(Id) FROM AgrBudget) = 0
		SET	@_budgetSeed	= 1
	ELSE
		SET	@_budgetSeed	= IDENT_CURRENT('AgrBudget') + 1
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	C O S T  C E N T E R  S E E D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededCCIDs')
		DROP TABLE tmpSeededCCIDs

	DECLARE	@_ccSeed	int

	IF (SELECT Count(Id) FROM AgrCostCenter) = 0
		SET	@_ccSeed		= 1
	ELSE
		SET	@_ccSeed		= IDENT_CURRENT('AgrCostCenter') + 1
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S P O N S O R  S E E D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededSponsorIDs')
		DROP TABLE tmpSeededSponsorIDs

	DECLARE	@_sponsorSeed			int

	IF (SELECT Count(Id) FROM AdmSponsor) = 0
		SET	@_sponsorSeed	= 1
	ELSE
		SET	@_sponsorSeed	= IDENT_CURRENT('AdmSponsor') + 1

	DECLARE	@_sql	varchar(1000)
/*
	C R E A T E  F O L D E R  N U M B E R S
	Get the last folder number created by Insight and create new folder numbers
*/
	declare @NextFolderNumber as int
	declare @fiscalYear as int
	if month(getdate())>9 set @fiscalYear = year(getdate())+1 else set @fiscalYear = year(getdate())

	Select @NextFolderNumber = isNull(right(max(foldernumber),7),0)+1 from agragreement where foldernumber like cast(@FiscalYear as varchar) + 'I%'

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFolderNumbers')
		DROP TABLE tmpSeededFolderNumbers

	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@NextFolderNumber AS varchar) + ', 1) AS FolderID, cast(ID as int) as ID INTO dbo.tmpSeededFolderNumbers FROM Stg_IE_DemogFeed  where IsNewAgreement=1 and isnull(folder_number,'''')='''' and IsValid = 1 and IsAgreementMaintained = 1'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFolderNumbers') Begin
		-- NEW Folder numbers
		UPDATE s
		SET folder_number = cast(@FiscalYear as varchar) + 'I' + right('000000' + cast(t.FolderID as varchar),7)
		FROM tmpSeededFolderNumbers t
		inner join Stg_IE_DemogFeed s on s.ID = t.ID

		DROP TABLE dbo.tmpSeededFolderNumbers
	End

	-- now get distinct new folder numbers
	SELECT	DISTINCT folder_number as FolderNumber
	INTO		#NewFolderNumbers
	FROM		Stg_IE_DemogFeed
	WHERE		FwkResourceId IS NULL and IsNull(ltrim(rtrim(folder_number)),'')<>'' and IsAgreementMaintained = 1 
	ORDER BY	FolderNumber

	IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE NAME like '#NewFolderNumbers%') begin

		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_resourceSeed AS varchar) + ', 1) AS FwkId, FolderNumber INTO dbo.tmpSeededFwkIDs FROM #NewFolderNumbers'
		EXEC(@_sql)

		UPDATE	Stg_IE_DemogFeed
		SET		FwkResourceId = tmpSeededFwkIDs.FwkId
		FROM	tmpSeededFwkIDs
		WHERE	Stg_IE_DemogFeed.folder_number = tmpSeededFwkIDs.FolderNumber

		select @_resourceSeed = coalesce((select max(tmpSeededFwkIDs.FwkId)+1 from tmpSeededFwkIDs),@_resourceSeed)

		DROP TABLE tmpSeededFwkIDs

		-- AgrBudgetContainerId
		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_budgetContainerSeed AS varchar) + ', 1) AS BgtId, FolderNumber INTO dbo.tmpSeededBgtIDs FROM #NewFolderNumbers'
		EXEC(@_sql)

		UPDATE	Stg_IE_DemogFeed
		SET		AgrBudgetContainerId = tmpSeededBgtIDs.BgtId
		FROM	tmpSeededBgtIDs
		WHERE	Stg_IE_DemogFeed.folder_number = tmpSeededBgtIDs.FolderNumber

		DROP TABLE tmpSeededBgtIDs

		-- AnnAnnotationContainerId
		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_annotationSeed AS varchar) + ', 1) AS AnnId, FolderNumber INTO dbo.tmpSeededAnnIDs FROM #NewFolderNumbers'
		EXEC(@_sql)

		UPDATE	Stg_IE_DemogFeed
		SET		AnnAnnotationContainerId = tmpSeededAnnIDs.AnnId
		FROM	tmpSeededAnnIDs
		WHERE	Stg_IE_DemogFeed.folder_number = tmpSeededAnnIDs.FolderNumber
	
		DROP TABLE tmpSeededAnnIDs


		-- AgrAgreementId
		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_agreementSeed AS varchar) + ', 1) AS AgrId, FolderNumber INTO dbo.tmpSeededAgrIDs FROM #NewFolderNumbers'
		EXEC(@_sql)

		UPDATE	Stg_IE_DemogFeed
		SET		AgrAgreementId = tmpSeededAgrIDs.AgrId
		FROM	tmpSeededAgrIDs
		WHERE	Stg_IE_DemogFeed.folder_number = tmpSeededAgrIDs.FolderNumber

		DROP TABLE tmpSeededAgrIDs

		-- SvcEntityId	
		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_entitySeed AS varchar) + ', 1) AS SvcId, FolderNumber INTO dbo.tmpSeededSvcIDs FROM #NewFolderNumbers'
		EXEC(@_sql)

		UPDATE	Stg_IE_DemogFeed
		SET		SvcEntityId = tmpSeededSvcIDs.SvcId
		FROM	tmpSeededSvcIDs
		WHERE	Stg_IE_DemogFeed.folder_number = tmpSeededSvcIDs.FolderNumber

		-- get the next service entity ID seed to be used in creation of funds.

		select @_entitySeed = coalesce((select max(tmpSeededSvcIDs.SvcId)+1 from tmpSeededSvcIDs),@_entitySeed)

		DROP TABLE tmpSeededSvcIDs
		DROP TABLE #NewFolderNumbers
	End

	-- SvcEntityId for FUNDS
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_entitySeed AS varchar) + ', 1) AS SvcId, cast(ID as int) as ID INTO dbo.tmpSeededFundSvcIDs FROM Stg_IE_DemogFeed where IsNewFund=1 and IsValid = 1'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFundSvcIDs') Begin
		UPDATE	Stg_IE_DemogFeed
		SET		AgrFundSvcEntityId = tmpSeededFundSvcIDs.SvcId
		FROM	tmpSeededFundSvcIDs
		WHERE	Stg_IE_DemogFeed.ID = tmpSeededFundSvcIDs.ID

		DROP TABLE tmpSeededFundSvcIDs
	End

	-- AgrCostCenter.Id for only those that are not defined.
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_ccSeed AS varchar) + ', 1) AS MapId, cast(ID as int) as ID INTO dbo.tmpSeededCCIDs FROM Stg_IE_DemogFeed where IsNewCostCenter = 1 and IsValid = 1'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededCCIDs') Begin
		UPDATE	Stg_IE_DemogFeed
		SET		AgrCostCenterId = tmpSeededCCIDs.MapId
		FROM	tmpSeededCCIDs
		WHERE	Stg_IE_DemogFeed.ID		= tmpSeededCCIDs.ID

		DROP TABLE tmpSeededCCIDs
	End

/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	F U N D  M A P P I N G
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	-- Insert fund subtype if none exists.
	INSERT INTO AgrFundSubType (Name,Description,FundNumberPrefix,FundTypeId)
	SELECT	'Research Grant ' + SUBSTRING(Stg_IE_DemogFeed.project_grant,1,1),
			'Research Grant ' + SUBSTRING(Stg_IE_DemogFeed.project_grant,1,1),
			SUBSTRING(Stg_IE_DemogFeed.project_grant,1,1),
			1
	FROM	Stg_IE_DemogFeed
	WHERE	NOT EXISTS
			(
				SELECT	1 FROM AgrFundSubType 
				WHERE	AgrFundSubType.FundTypeId = 1
				AND	(	AgrFundSubType.FundNumberPrefix = SUBSTRING(Stg_IE_DemogFeed.project_grant,1,3)
				OR		AgrFundSubType.FundNumberPrefix = SUBSTRING(Stg_IE_DemogFeed.project_grant,1,2)
				OR		AgrFundSubType.FundNumberPrefix = SUBSTRING(Stg_IE_DemogFeed.project_grant,1,1)	)
			)

	-- get IDs for all the FKs
	-- fund sub-type Id
	UPDATE		Stg_IE_DemogFeed
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 1
	AND			SUBSTRING(Stg_IE_DemogFeed.project_grant,1,3) = AgrFundSubType.FundNumberPrefix

	UPDATE		Stg_IE_DemogFeed
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 1
	AND			SUBSTRING(Stg_IE_DemogFeed.project_grant,1,2) = AgrFundSubType.FundNumberPrefix
	AND			Stg_IE_DemogFeed.AgrFundSubTypeId IS NULL

	UPDATE		Stg_IE_DemogFeed
	SET			AgrFundSubTypeId = AgrFundSubType.Id
	FROM		AgrFundSubType
	WHERE		AgrFundSubType.FundTypeId = 1
	AND			SUBSTRING(Stg_IE_DemogFeed.project_grant,1,1) = AgrFundSubType.FundNumberPrefix
	AND			Stg_IE_DemogFeed.AgrFundSubTypeId IS NULL

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  F U N D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	-- AgrFund.Id
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_fundSeed AS varchar) + ', 1) AS MapId, cast(ID as int) as ID INTO dbo.tmpSeededFundIDs FROM Stg_IE_DemogFeed where AgrFundId is null and isnull(project_grant,'''')<> '''' and IsValid = 1'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFundIDs') Begin
		--Create FwkResourceIDs for funds.
		if object_id('tmpSeededFwkIDs') is not null drop table dbo.tmpSeededFwkIDs
		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_resourceSeed AS varchar) + ', 1) AS FwkId, cast(ID as int) as ID INTO dbo.tmpSeededFwkIDs FROM Stg_IE_DemogFeed where AgrFundId is null and isnull(project_grant,'''')<> '''' and IsValid = 1'
		EXEC(@_sql)

		UPDATE	Stg_IE_DemogFeed
		SET		AgrFundId = tmpSeededFundIDs.MapId
		FROM	tmpSeededFundIDs
		WHERE	Stg_IE_DemogFeed.ID = tmpSeededFundIDs.ID

		DROP TABLE tmpSeededFundIDs

		UPDATE	Stg_IE_DemogFeed
		SET		FundFwkResourceId = tmpSeededFwkIDs.FwkId
		FROM	tmpSeededFwkIDs
		WHERE	Stg_IE_DemogFeed.Id = tmpSeededFwkIDs.Id

		select @_resourceSeed = coalesce((select max(tmpSeededFwkIDs.FwkId)+1 from tmpSeededFwkIDs),@_resourceSeed)

		DROP TABLE tmpSeededFwkIDs

	End

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  A G R E E M E N T  F U N D  M A P S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	-- AgrFund_AgrAgreement_Map.Id
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_fundAgreeMapSeed AS varchar) + ', 1) AS MapId, cast(s.ID as int) as ID INTO dbo.tmpSeededFundAgreeMapIDs FROM Stg_IE_DemogFeed s where isnull(s.project_grant,'''')<> '''' and not exists(Select 1 from AgrFund_AgrAgreement_Map m where m.agrFundId = s.agrFundId and m.agragreementId = s.agrAgreementId) and s.IsValid = 1 and s.agragreementid is not null'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFundAgreeMapIDs') Begin
		UPDATE	Stg_IE_DemogFeed
		SET		AgrFundAgreementMapId = tmpSeededFundAgreeMapIDs.MapId
		FROM	tmpSeededFundAgreeMapIDs
		WHERE	Stg_IE_DemogFeed.ID = tmpSeededFundAgreeMapIDs.ID

		DROP TABLE tmpSeededFundAgreeMapIDs
	End
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  S P O N S O R S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/

	-- now get distinct new sponsor numbers
	SELECT	DISTINCT sponsor_name as sponsorName,sponsor_code_id as SponsorCode
	INTO		#tmp2
	FROM		Stg_IE_DemogFeed
	WHERE		IsNewSponsor = 1 and IsValid = 1 and isnull(sponsor_name,'')<> '' and admSponsorId is null
	ORDER BY	sponsor_name,sponsor_code_id

	IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE NAME like '#tmp2%') begin
		-- AdmSponsor.Id
		SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_sponsorSeed AS varchar) + ', 1) AS MapId, SponsorName, SponsorCode  INTO dbo.tmpSeededSponsorIDs FROM #TMP2'
		EXEC(@_sql)

		Drop Table #tmp2

		IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededSponsorIDs') Begin
			UPDATE	Stg_IE_DemogFeed
			SET		AdmSponsorId = tmpSeededSponsorIDs.MapId
			FROM	tmpSeededSponsorIDs
			WHERE	Stg_IE_DemogFeed.Sponsor_Name = tmpSeededSponsorIDs.SponsorName
				and Stg_IE_DemogFeed.Sponsor_Code_id = tmpSeededSponsorIDs.sponsorCode

			DROP TABLE tmpSeededSponsorIDs
		End
	End

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I D E N T I F Y  C U R R E N T  F U N D S
	Identify and flag the current fund for each agreement.  The current fund is
	the fund that has the latest budget start date.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE s set IsCurrentFund = 1
	From Stg_IE_DemogFeed s
	Where	s.id = (select max(s1.id) from Stg_IE_DemogFeed s1 
					where s.Folder_number = s1.Folder_number
						AND IsNull(s1.dtbudgetStart,'') = (select max(IsNull(s2.dtbudgetStart,'')) 
															from Stg_IE_DemogFeed s2
															where s1.Folder_number = s2.Folder_number
																and s2.IsValid = 1))
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  B U D G E T  C O N T A I N E R  V I E W
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	-- AgrFund_AgrAgreement_Map.Id
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_budgetContainerViewSeed AS varchar) + ', 1) AS MapId, cast(s.ID as int) as ID INTO dbo.tmpSeededBudgetContainerViewIDs FROM Stg_IE_DemogFeed s where s.AgrBudgetContainerViewId is null AND s.AgrBudgetContainerId is not null and s.IsValid = 1 and s.IsCurrentFund= 1'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededBudgetContainerViewIDs') Begin
		UPDATE	Stg_IE_DemogFeed
		SET		AgrBudgetContainerViewId = tmpSeededBudgetContainerViewIDs.MapId
		FROM	tmpSeededBudgetContainerViewIDs
		WHERE	Stg_IE_DemogFeed.ID = tmpSeededBudgetContainerViewIDs.ID

		DROP TABLE tmpSeededBudgetContainerViewIDs
	End
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  B U D G E T S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	-- AgrFund_AgrAgreement_Map.Id
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_budgetSeed AS varchar) + ', 1) AS MapId, cast(s.ID as int) as ID INTO dbo.tmpSeededBudgetIDs FROM Stg_IE_DemogFeed s where s.AgrBudgetId is null and s.AgrBudgetContainerViewId is not null and s.IsValid = 1'
	EXEC(@_sql)

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededBudgetIDs') Begin
		UPDATE	Stg_IE_DemogFeed
		SET		AgrBudgetId = tmpSeededBudgetIDs.MapId
		FROM	tmpSeededBudgetIDs
		WHERE	Stg_IE_DemogFeed.ID = tmpSeededBudgetIDs.ID

		DROP TABLE tmpSeededBudgetIDs
	End

/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E R M I N E  C H A N G E S
	Changes come in the form of a new Id value for most data in this feed.  There are
	a small number of individual data items that also get updated that must be checked
	to see if they changed.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE s set IsChanged = 1
	From Stg_IE_DemogFeed s
	left outer join agragreement aa on aa.id = s.agragreementid
	left outer join AgrFund_agragreement_map afam on afam.agragreementid = s.agragreementid and afam.agrfundid = s.agrfundid
	left outer join SvcEntity_AdmSponsor_Map asm on asm.svcentityid = s.svcentityid and asm.AdmSponsorRelationTypeId = 1
	WHERE
		-- Agreement Changes
		((IsNull(s.Orig_AgrTransactionTypeId,0) <> IsNull(s.AgrTransactionTypeId,0)
		or IsNull(s.Orig_AgrAgreementTypeId,0) <>  IsNull(s.AgrAgreementTypeId,0)
		or IsNull(s.Orig_AgrAgreementStatusTypeId,0) <>  IsNull(s.AgrAgreementStatusTypeId,0)
		or IsNull(s.Orig_PI_AdmPersonID,0) <>  IsNull(s.PI_AdmPersonID,0)
		or IsNull(s.Orig_AdmSponsorId,0) <>  IsNull(s.AdmSponsorId,0)
		or IsNull(s.Orig_fwkDomainOrganizationId,0) <>  IsNull(s.fwkDomainOrganizationId,0)
		or IsNull(s.Orig_finICBaseTypeId,0) <> IsNull(s.finICBaseTypeId,0)
		or IsNull(s.Orig_AgrActivityTypeId,0) <>  IsNull(s.AgrActivityTypeId,0)
		or IsNull(s.Orig_FinAccountCycleTypeId,0) <> IsNull(s.FinAccountCycleTypeId,0)
		or IsNull(s.Orig_admfacilityId,0) <> IsNull(s.AdmFacilityId,0)
	 		-- INDIVIDUAL column change checking below
		or left(convert(varchar,lower(IsNull(aa.ShortTitle,''))),30) <> lower(IsNull(s.description,''))
		or IsNull(aa.ProjectStartDate,'') <> IsNull(s.dtProjectStart,'')
		or IsNull(aa.ProjectEndDate,'') <> IsNull(s.dtProjectEnd,'')
		or IsNull(asm.SponsorNumber,'') <> IsNull(s.sponsor_agree_id_format,''))
		and IsNull(s.IsCurrentFund,0) = 1)

		or 
		--Fund Changes 
		IsNull(s.Orig_AgrFundStatusTypeId,0) <> IsNull(s.AgrFundStatusTypeId,0)
		or IsNull(s.Orig_AgrFundAwardTypeId,0) <> IsNull(s.AgrFundAwardTypeId,0)
		or IsNull(s.Orig_AgrFundOverheadClassificationId,0) <> IsNull(s.AgrFundOverheadClassificationId,0)
		or IsNull(s.Orig_AgrFundTypeId,0) <> IsNull(s.AgrFundTypeId,0)
		or IsNull(s.Orig_AgrFundSubTypeId,0) <> IsNull(s.AgrFundSubTypeId,0)
		or (((select cc.Number from AgrCostCenter cc where cc.Id =IsNull(s.Orig_AgrCostCenterId,0)) <> IsNull(s.Cost_Center,'')
		or IsNull(s.Orig_AgrCostCenterStatusTypeId,0) <> IsNull(s.AgrCostCenterStatusTypeId,0)) and IsNull(resp_inst,'') = 'BWH')
	 		-- INDIVIDUAL column change checking below
		or IsNull(afam.CFDA,'') <> IsNull(s.cfda_number,'')
		or IsNull(afam.DocumentLetter,'') <> IsNull(s.doc_letter,'')
		or left(lower(IsNull(afam.FundShortTitle,'')),30) <> lower(IsNull(s.Description,''))
		or lower(IsNull(afam.FundTitle,'')) <> left(lower(IsNull(s.long_Description,'')),128)

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Create a table of all the folder numbers sorted in the order of the new agreement
	report
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	select identity(int,1,1) as Id,cast(s.id as int) as stagingId,s.folder_number
		, cast('' as varchar(15)) as PreviousFolderNumber
	into #PreviousFolder 
	from Stg_IE_DemogFeed s 
	where s.Isvalid = 1
	order by s.folder_number, s.dtbudgetstart, CAST(s.Id AS INT)

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Update the previous folder number column that goes with the folder number.
	This will be used to break on folder numbers in the report.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	update t 
	set PreviousFolderNumber = IsNull((select t1.folder_number 
								 from #PreviousFolder t1 where t1.Id = (t.id-1)),'')
	from #PreviousFolder t 

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Create the Report output including the previous folder number.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	update s
	Set  PreviousFolderNumber = pf.PreviousFolderNumber 
	from Stg_IE_DemogFeed s
	inner join #PreviousFolder pf on pf.StagingId = s.Id

	drop table #PreviousFolder
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  E R R O R S
	Look for stuff that does not map
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE	Stg_IE_DemogFeed
	SET		IsValid = 0
	Where	IsValid = 1 and
		   (isNull(AgrFundId,0)=0 
		or isNull(AgrAgreementId,0)=0 
		or isNull(SvcEntityId,0)=0 
		or isNull(FwkResourceId,0)=0 
		--or isNull(AgrFundAgreementMapId,0)=0 
		or (isNull(AgrBudgetContainerId,0)=0 
		AND isNull(AgrBudgetContainerViewId,0)<>0))

	SET NOCOUNT OFF

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO